﻿using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

#if ExcelByEPPlus

using OfficeOpenXml;

namespace Library.Excel
{
    /// <summary>
    /// 引用 EPPlus.dll
    /// </summary>
    public class ExcelByEPPlus : ExcelByBase
    {
        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static IEnumerable<DataTable> ImportExcelToDataTable(string file, int keyLine = -1, int SkipRows = 0, int SkipColumns = 0, int TakeColumns = int.MaxValue)
        {
            using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(file)))
            {
                foreach (var worksheet in excelPackage.Workbook.Worksheets)
                {
                    var dataTable = new DataTable();

                    dataTable.TableName = worksheet.Name;

                    if (keyLine == -1)
                    {
                        for (int i = 0; i < worksheet.Dimension.Columns; i++)
                        {
                            dataTable.Columns.Add();
                        }
                    }
                    else
                    {
                        foreach (var item in worksheet.Cells.Skip(keyLine * worksheet.Dimension.Columns).Take(worksheet.Dimension.Columns).Skip(SkipColumns).Take(TakeColumns).Select(p => p.GetValue<object>()))
                        {
                            dataTable.Columns.Add(item.ToString());
                        }
                    }

                    for (int j = SkipRows; j < worksheet.Dimension.Rows; j++)
                    {
                        dataTable.Rows.Add(worksheet.Cells.Skip(j * worksheet.Dimension.Columns).Take(worksheet.Dimension.Columns).Skip(SkipColumns).Take(TakeColumns).Select(p => p.GetValue<object>()).ToArray());
                    }

                    yield return dataTable;
                }
            }
        }

        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="file">导出路径(包括文件名与扩展名)</param>
        /// <param name="dts"></param>
        public static void ExportDataTableToExcel(string file, params DataTable[] dts)
        {
            using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(file)))
            {
                foreach (var dt in dts)
                {
                    var worksheet = excelPackage.Workbook.Worksheets.Add(dt.TableName);

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            worksheet.SetValue(i, j, dt.Rows[j]);
                        }
                    }
                }
            }
        }
    }
}
#endif